#!/bin/bash
# Oracle 数据库巡检脚本
# 作者：hanhan
# 版本：1.3
# 功能：收集 Oracle 数据库关键信息并生成巡检报告
# 执行方式：bash <(curl -s -L https://gitee.com/securitypass/auto-scirpt/raw/master/linux/oracle%E5%B7%A1%E6%A3%80%E8%84%9A%E6%9C%AC.sh)
# 注意：脚本需要在 Oracle 数据库服务器上执行，并且需要具有 sysdba 权限。

# 设置环境变量
export LANG=en_US.UTF-8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

# 定义颜色
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[0;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 定义变量
SCRIPT_DIR="$(pwd)"
REPORT_DIR="${SCRIPT_DIR}/reports"
DATE_TIME=$(date +"%Y%m%d_%H%M%S")
REPORT_FILE="${REPORT_DIR}/Oracle_巡检报告_${DATE_TIME}.md"
HTML_REPORT="${REPORT_DIR}/Oracle_巡检报告_${DATE_TIME}.html"
TMP_DIR="${SCRIPT_DIR}/tmp_${DATE_TIME}"
LOG_FILE="${SCRIPT_DIR}/oracle_check_${DATE_TIME}.log"
START_TIME=$(date +%s)  # 脚本开始执行的时间
TOTAL_STEPS=12          # 总步骤数
CURRENT_STEP=0          # 当前步骤

# 确保报告目录和临时目录存在
mkdir -p "$REPORT_DIR"
mkdir -p "$TMP_DIR"
mkdir -p "$(dirname "$LOG_FILE")"

# 输出日志函数，附带时间戳
log() {
    local level="$1"
    local message="$2"
    local color=""
    
    case "$level" in
        "INFO") color="${GREEN}" ;;
        "WARN") color="${YELLOW}" ;;
        "ERROR") color="${RED}" ;;
        *) color="${NC}" ;;
    esac
    
    local log_message="$(date '+%Y-%m-%d %H:%M:%S') [$level] $message"
    echo -e "${color}${log_message}${NC}"
    echo "$log_message" >> "$LOG_FILE"
}

# 显示进度
show_progress() {
    CURRENT_STEP=$((CURRENT_STEP + 1))
    local percent=$((CURRENT_STEP * 100 / TOTAL_STEPS))
    log "INFO" "进度: [$CURRENT_STEP/$TOTAL_STEPS] $percent% - $1"
}

# 检查必要的命令是否存在
check_prerequisites() {
    show_progress "检查必要条件"
    
    # 检查用户权限
    if [[ $(whoami) != "oracle" ]]; then
        log "WARN" "当前用户不是oracle用户，可能无法正确获取数据库信息"
        log "INFO" "当前用户: $(whoami)"
    fi
    
    # 检查是否具有sysdba权限
    if ! sqlplus -S / as sysdba <<< "exit" > /dev/null 2>&1; then
        log "ERROR" "当前用户没有sysdba权限，请切换到具有sysdba权限的用户或使用sysdba用户执行脚本"
        exit 1
    fi
    
    # 检查sqlplus命令
    if ! command -v sqlplus &> /dev/null; then
        log "ERROR" "未找到sqlplus命令，请确保Oracle客户端已正确安装"
        exit 1
    fi
    
    # 检查是否设置了ORACLE_HOME和ORACLE_SID
    if [[ -z "$ORACLE_HOME" ]]; then
        log "WARN" "ORACLE_HOME环境变量未设置，尝试自动检测"
        # 尝试从进程中获取ORACLE_HOME
        ORACLE_HOME=$(ps -ef | grep pmon | grep -v grep | awk '{print $8}' | sed 's/\/dbs\/.*$//')
        if [[ -n "$ORACLE_HOME" ]]; then
            export ORACLE_HOME
            log "INFO" "自动设置ORACLE_HOME=$ORACLE_HOME"
        else
            log "ERROR" "无法自动检测ORACLE_HOME，请手动设置"
            exit 1
        fi
    fi
    
    if [[ -z "$ORACLE_SID" ]]; then
        log "WARN" "ORACLE_SID环境变量未设置，尝试自动检测"
        # 尝试从进程中获取ORACLE_SID
        ORACLE_SID=$(ps -ef | grep pmon | grep -v grep | awk '{print $8}' | sed 's/.*pmon_//')
        if [[ -n "$ORACLE_SID" ]]; then
            export ORACLE_SID
            log "INFO" "自动设置ORACLE_SID=$ORACLE_SID"
        else
            log "ERROR" "无法自动检测ORACLE_SID，请手动设置"
            exit 1
        fi
    fi
    
    # 检查PATH中是否包含ORACLE_HOME/bin
    if [[ ! "$PATH" =~ "$ORACLE_HOME/bin" ]]; then
        export PATH=$ORACLE_HOME/bin:$PATH
        log "INFO" "将$ORACLE_HOME/bin添加到PATH"
    fi
}

# 计算脚本执行时间
get_execution_time() {
    local end_time=$(date +%s)
    local duration=$((end_time - START_TIME))
    local hours=$((duration / 3600))
    local minutes=$(( (duration % 3600) / 60))
    local seconds=$((duration % 60))
    echo "$hours小时 $minutes分钟 $seconds秒"
}

# 执行SQL并捕获错误
run_sql() {
    local sql="$1"
    local output_file="${TMP_DIR}/sql_output_$(date +%s%N).txt"
    local error_file="${TMP_DIR}/sql_error_$(date +%s%N).txt"
    
    sqlplus -S / as sysdba > "$output_file" 2> "$error_file" <<EOF
    set linesize 200
    set pagesize 50
    set feedback off
    set verify off
    set heading on
    set echo off
    set termout on
    set trimspool on
    $sql
    exit;
EOF
    
    if [[ -s "$error_file" ]]; then
        log "ERROR" "执行SQL时出错: $(cat "$error_file")"
        echo "SQL执行错误: $(cat "$error_file")"
    else
        cat "$output_file"
    fi
    
    rm -f "$output_file" "$error_file"
}

# 检查 Oracle 是否正常启动
check_oracle_status() {
    show_progress "检查Oracle数据库状态"
    log "INFO" "检查Oracle数据库状态..."
    
    # 直接使用 sqlplus 执行简单命令
    sqlplus -S / as sysdba <<EOF > "${TMP_DIR}/db_status.txt" 2> "${TMP_DIR}/db_status_err.txt"
set heading off
set feedback off
set pagesize 0
select status from v\$instance;
exit;
EOF
    
    # 检查是否有错误
    if [ -s "${TMP_DIR}/db_status_err.txt" ]; then
        local error=$(cat "${TMP_DIR}/db_status_err.txt")
        log "ERROR" "检查数据库状态时出错: $error"
        echo "数据库状态检查失败: $error"
        return 1
    fi
    
    # 读取状态并去除空白
    local db_status=$(cat "${TMP_DIR}/db_status.txt" | tr -d '[:space:]')
    
    if [[ "$db_status" == "OPEN" ]]; then
        log "INFO" "Oracle数据库正常启动，状态：$db_status"
        echo "数据库状态: $db_status (正常)"
        return 0
    else
        log "WARN" "Oracle数据库状态异常，当前状态：$db_status"
        echo "数据库状态: $db_status (异常)"
        return 1
    fi
}

# 获取数据库版本信息
get_db_version() {
    show_progress "获取数据库版本信息"
    log "INFO" "获取数据库版本信息..."
    
    local version=$(run_sql "
    set heading off
    select * from v\$version where banner like 'Oracle%';
    ")
    log "INFO" "数据库版本信息：$version"
    echo "$version"
}

# 获取数据库基本信息
get_db_info() {
    show_progress "获取数据库基本信息"
    log "INFO" "获取数据库基本信息..."
    
    local db_info=$(run_sql "
    select name, created, log_mode, open_mode, database_role from v\$database;
    select instance_name, host_name, version, startup_time, status from v\$instance;
    ")
    
    log "INFO" "数据库基本信息已获取"
    echo "$db_info"
}

# 获取表空间使用情况
check_tablespaces() {
    show_progress "检查表空间使用情况"
    log "INFO" "获取表空间使用情况..."
    
    local tablespace_info=$(run_sql "
    column tablespace_name format a20
    column used_space format 99999.99
    column total_space format 99999.99
    column percent_used format 999.99
    column status format a10
    
    select 
        a.tablespace_name, 
        round(a.used_space,2) used_space_mb, 
        round(b.total_space,2) total_space_mb, 
        round(a.used_space/b.total_space*100,2) percent_used,
        case 
            when round(a.used_space/b.total_space*100,2) > 90 then '警告'
            when round(a.used_space/b.total_space*100,2) > 80 then '注意'
            else '正常'
        end as status
    from 
        (select tablespace_name, sum(bytes)/1024/1024 used_space 
         from dba_segments group by tablespace_name) a,
        (select tablespace_name, sum(bytes)/1024/1024 total_space 
         from dba_data_files group by tablespace_name) b
    where 
        a.tablespace_name = b.tablespace_name
    order by 
        percent_used desc;
    ")
    
    log "INFO" "表空间使用情况已获取"
    echo "$tablespace_info"
}

# 获取当前活动会话
check_sessions() {
    show_progress "检查活动会话"
    log "INFO" "获取当前会话信息..."
    
    local session_info=$(run_sql "
    column username format a15
    column status format a10
    column machine format a20
    column program format a30
    column sid format 9999
    column serial# format 99999
    column logon_time format a20
    
    select 
        s.sid, 
        s.serial#, 
        s.username, 
        s.status, 
        s.machine, 
        s.program, 
        to_char(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') logon_time,
        round(s.last_call_et/60,2) idle_minutes
    from 
        v\$session s
    where 
        s.status = 'ACTIVE' 
        and s.username is not null
    order by 
        s.last_call_et desc;
    ")
    
    log "INFO" "活动会话信息已获取"
    echo "$session_info"
}

# 检查锁定的对象
check_locked_objects() {
    show_progress "检查锁定的对象"
    log "INFO" "检查锁定的对象..."
    
    local locked_objects=$(run_sql "
    column username format a15
    column os_user format a15
    column object_owner format a15
    column object_name format a30
    column object_type format a15
    column locked_mode format a15
    
    select 
        b.session_id, 
        c.username,           -- 修改这里，使用 username 而不是 oracle_username
        c.osuser as os_user,  -- 修改这里，使用 osuser 而不是 os_user_name
        b.owner object_owner, 
        b.object_name, 
        b.object_type, 
        decode(a.locked_mode, 
               0, 'None', 
               1, 'Null', 
               2, 'Row-S (SS)', 
               3, 'Row-X (SX)', 
               4, 'Share', 
               5, 'S/Row-X (SSX)', 
               6, 'Exclusive', 
               to_char(a.locked_mode)) locked_mode,
        c.machine,
        c.program
    from 
        v\$locked_object a, 
        dba_objects b, 
        v\$session c
    where 
        a.object_id = b.object_id
        and a.session_id = c.sid
    order by 
        c.username, b.object_name;
    ")
    
    if [[ -z "$locked_objects" || "$locked_objects" =~ "no rows selected" ]]; then
        log "INFO" "没有发现锁定的对象"
        echo "没有发现锁定的对象"
    else
        log "WARN" "发现锁定的对象"
        echo "$locked_objects"
    fi
}

# 检查数据库的等待事件
check_wait_events() {
    show_progress "检查等待事件"
    log "INFO" "检查数据库的等待事件..."
    
    local wait_events=$(run_sql "
    column event format a40
    column total_waits format 999,999,999
    column time_waited format 999,999,999
    column average_wait format 999.99
    
    select 
        event, 
        total_waits, 
        time_waited, 
        round(time_waited/total_waits,2) average_wait
    from 
        v\$system_event 
    where 
        total_waits > 0 
        and event not like '%idle%'
        and event not like '%rdbms ipc%'
        and event not like '%SQL*Net%'
        and ROWNUM <= 20
    order by 
        time_waited desc;
    ")
    
    log "INFO" "等待事件信息已获取"
    echo "$wait_events"
}

# 获取数据库的 CPU、内存和磁盘使用情况
check_system_resources() {
    show_progress "检查系统资源"
    log "INFO" "检查系统资源使用情况..."
    
    # 检查 CPU 使用情况
    local cpu_usage=$(top -bn1 | grep "Cpu(s)" | sed "s/.*, *\([0-9.]*\)%* id.*/\1/" | awk '{print 100 - $1}')
    log "INFO" "CPU 使用率：$cpu_usage%"
    
    # 检查内存使用情况
    local memory_info=$(free -m | awk 'NR==2{printf "总内存: %s MB, 已用: %s MB, 空闲: %s MB, 使用率: %.2f%%", $2, $3, $4, $3/$2*100}')
    log "INFO" "内存使用情况：$memory_info"
    
    # 检查磁盘使用情况
    local disk_usage=$(df -h | grep -E '^/dev' | awk '{ print "分区: " $1 ", 总大小: " $2 ", 已用: " $3 ", 可用: " $4 ", 使用率: " $5 ", 挂载点: " $6 }')
    log "INFO" "磁盘使用情况：$disk_usage"
    
    # 检查 Oracle 内存使用情况
    local oracle_memory=$(run_sql "
    select 
        name, 
        value/1024/1024 as value_mb 
    from 
        v\$parameter 
    where 
        name in ('sga_max_size', 'sga_target', 'pga_aggregate_target');
    ")
    
    log "INFO" "Oracle 内存参数：$oracle_memory"
    
    # 输出结果
    echo "## CPU 使用情况"
    echo "CPU 使用率: $cpu_usage%"
    echo ""
    echo "## 内存使用情况"
    echo "$memory_info"
    echo ""
    echo "## Oracle 内存参数"
    echo "$oracle_memory"
    echo ""
    echo "## 磁盘使用情况"
    echo "$disk_usage"
}

# 检查数据库备份
check_backups() {
    show_progress "检查数据库备份"
    log "INFO" "检查数据库备份..."
    
    # 检查RMAN备份历史
    local rman_backups=$(run_sql "
    column start_time format a20
    column end_time format a20
    column status format a10
    column input_type format a15
    column elapsed_seconds format 999,999
    column output_mbytes format 999,999.99
    
    select 
        to_char(start_time, 'YYYY-MM-DD HH24:MI:SS') start_time,
        to_char(end_time, 'YYYY-MM-DD HH24:MI:SS') end_time,
        status,
        input_type,
        elapsed_seconds,
        output_bytes/1024/1024 output_mbytes
    from 
        v\$rman_backup_job_details
    where 
        start_time > sysdate - 7
    order by 
        start_time desc;
    ")
    
    # 检查控制文件备份记录
    local controlfile_backups=$(run_sql "
    column backup_type format a15
    column completion_time format a20
    column status format a10
    column mbytes_processed format 999,999.99
    
    select 
        backup_type,
        to_char(completion_time, 'YYYY-MM-DD HH24:MI:SS') completion_time,
        status,
        blocks*block_size/1024/1024 mbytes_processed
    from 
        v\$backup_set
    where 
        completion_time > sysdate - 7
    order by 
        completion_time desc;
    ")
    
    log "INFO" "备份信息已获取"
    
    echo "## RMAN备份历史（最近7天）"
    if [[ -z "$rman_backups" || "$rman_backups" =~ "no rows selected" ]]; then
        echo "最近7天没有RMAN备份记录"
    else
        echo "$rman_backups"
    fi
    
    echo ""
    echo "## 控制文件备份记录（最近7天）"
    if [[ -z "$controlfile_backups" || "$controlfile_backups" =~ "no rows selected" ]]; then
        echo "最近7天没有控制文件备份记录"
    else
        echo "$controlfile_backups"
    fi
}

# 检查归档日志
check_archivelog() {
    show_progress "检查归档日志"
    log "INFO" "检查归档日志状态..."
    
    # 检查归档模式
    local archivelog_mode=$(run_sql "
    set heading off
    select log_mode from v\$database;
    ")
    archivelog_mode=$(echo "$archivelog_mode" | tr -d '[:space:]')
    
    # 检查归档目标
    local archive_dest=$(run_sql "
    column destination format a50
    select destination from v\$archive_dest where status='VALID' and destination is not null;
    ")
    
    # 检查归档日志使用情况
    local archive_usage=$(run_sql "
    select 
        round(sum(blocks*block_size)/1024/1024,2) as size_mb 
    from 
        v\$archived_log 
    where 
        completion_time > sysdate - 1;
    ")
    
    log "INFO" "归档日志模式：$archivelog_mode"
    
    echo "## 归档日志模式"
    echo "当前模式: $archivelog_mode"
    if [[ "$archivelog_mode" == "ARCHIVELOG" ]]; then
        echo "状态: 已启用归档模式"
    else
        echo "状态: 未启用归档模式（警告：生产环境建议启用归档模式）"
    fi
    
    echo ""
    echo "## 归档日志目标"
    echo "$archive_dest"
    
    echo ""
    echo "## 最近24小时归档日志大小"
    echo "$archive_usage MB"
}

# 检查数据库参数
check_db_parameters() {
    show_progress "检查数据库参数"
    log "INFO" "检查数据库参数..."
    
    local db_params=$(run_sql "
    column name format a30
    column value format a50
    
    select name, value from v\$parameter 
    where name in ('db_block_size', 'sga_max_size', 'pga_aggregate_target', 
                  'processes', 'sessions', 'open_cursors', 'db_cache_size',
                  'shared_pool_size', 'log_buffer', 'db_recovery_file_dest_size');
    ")
    
    echo "$db_params"
}


# 检查数据库性能
check_db_performance() {
    show_progress "检查数据库性能"
    log "INFO" "检查数据库性能指标..."
    
    local performance_metrics=$(run_sql "
    column metric_name format a40
    column value format 999,999,999.99
    column unit format a15
    
    select 
        metric_name, 
        value, 
        metric_unit as unit
    from 
        v\$sysmetric 
    where 
        metric_name in (
            'CPU Usage Per Sec',
            'Database CPU Time Ratio',
            'Database Wait Time Ratio',
            'Memory Sorts Ratio',
            'Physical Reads Per Sec',
            'Physical Writes Per Sec',
            'User Transaction Per Sec',
            'SQL Service Response Time',
            'User Calls Per Sec',
            'Logons Per Sec',
            'Buffer Cache Hit Ratio',
            'Executions Per Sec',
            'Hard Parse Count Per Sec'
        )
        and group_id = 2
    order by 
        metric_name;
    ")
    
    log "INFO" "数据库性能指标已获取"
    echo "$performance_metrics"
}


# 生成报告
generate_report() {
    show_progress "生成巡检报告"
    log "INFO" "生成巡检报告..."

    echo "# Oracle 数据库巡检报告 📊" > "$REPORT_FILE"
    echo "" >> "$REPORT_FILE"
    
    echo "## 基本信息" >> "$REPORT_FILE"
    echo "- **巡检时间**: $(date '+%Y-%m-%d %H:%M:%S')" >> "$REPORT_FILE"
    echo "- **服务器主机名**: $(hostname)" >> "$REPORT_FILE"
    echo "- **操作系统**: $(cat /etc/os-release | grep "PRETTY_NAME" | cut -d'"' -f2)" >> "$REPORT_FILE"
    echo "- **内核版本**: $(uname -r)" >> "$REPORT_FILE"
    echo "- **Oracle SID**: $ORACLE_SID" >> "$REPORT_FILE"
    echo "- **Oracle Home**: $ORACLE_HOME" >> "$REPORT_FILE"
    echo "" >> "$REPORT_FILE"

    echo "## 数据库状态" >> "$REPORT_FILE"
    check_oracle_status >> "$REPORT_FILE"

    echo "## 数据库版本信息" >> "$REPORT_FILE"
    get_db_version >> "$REPORT_FILE"

    echo "## 数据库基本信息" >> "$REPORT_FILE"
    get_db_info >> "$REPORT_FILE"

    echo "## 表空间使用情况" >> "$REPORT_FILE"
    check_tablespaces >> "$REPORT_FILE"

    echo "## 当前活动会话" >> "$REPORT_FILE"
    check_sessions >> "$REPORT_FILE"

    echo "## 锁定的对象" >> "$REPORT_FILE"
    check_locked_objects >> "$REPORT_FILE"

    echo "## 等待事件" >> "$REPORT_FILE"
    check_wait_events >> "$REPORT_FILE"

    echo "## 系统资源使用情况" >> "$REPORT_FILE"
    check_system_resources >> "$REPORT_FILE"

    echo "## 数据库参数" >> "$REPORT_FILE"
    check_db_parameters >> "$REPORT_FILE"

    echo "## 数据库性能指标" >> "$REPORT_FILE"
    check_db_performance >> "$REPORT_FILE"

    echo "## 数据库备份状态" >> "$REPORT_FILE"
    check_backups >> "$REPORT_FILE"

    echo "## 归档日志状态" >> "$REPORT_FILE"
    check_archivelog >> "$REPORT_FILE"

    echo "" >> "$REPORT_FILE"
    echo "## 巡检结论与建议" >> "$REPORT_FILE"
    echo "- **执行时间**: $(get_execution_time)" >> "$REPORT_FILE"
    echo "- **生成时间**: $(date '+%Y-%m-%d %H:%M:%S')" >> "$REPORT_FILE"

    log "INFO" "巡检报告已生成: $REPORT_FILE"
}

# 清理临时文件
cleanup() {
    show_progress "清理临时文件"
    log "INFO" "清理临时文件..."
    
    if [[ -d "$TMP_DIR" ]]; then
        rm -rf "$TMP_DIR"
        log "INFO" "已删除临时目录: $TMP_DIR"
    fi
}

# 主流程
main() {
    log "INFO" "Oracle数据库巡检脚本开始执行..."
    
    # 检查必要条件
    check_prerequisites
    
    # 检查数据库状态
    check_oracle_status
    if [[ $? -ne 0 ]]; then
        log "ERROR" "数据库状态异常，巡检终止"
        exit 1
    fi
    
    # 生成报告
    generate_report
    
    # 清理临时文件
    cleanup
    
    log "INFO" "脚本执行结束，巡检报告已生成："
    log "INFO" "- Markdown报告: $REPORT_FILE"
    if [[ -f "$HTML_REPORT" ]]; then
        log "INFO" "- HTML报告: $HTML_REPORT"
    fi
    
    log "INFO" "总执行时间: $(get_execution_time)"
    
    echo -e "\n${GREEN}巡检完成！${NC}"
    echo -e "报告位置: ${BLUE}$REPORT_FILE${NC}"
    if [[ -f "$HTML_REPORT" ]]; then
        echo -e "HTML报告: ${BLUE}$HTML_REPORT${NC}"
    fi
    echo -e "日志文件: ${BLUE}$LOG_FILE${NC}"
}

# 捕获Ctrl+C信号
trap 'log "WARN" "脚本被用户中断"; cleanup; exit 1' INT

# 执行主流程
main